import { Provide, Inject, Init } from '@midwayjs/core';
import { DatabaseService } from './database.service';
import { promisify } from 'util';
import { IExcalidrawAttach, IExcalidrawAttachCreateOptions } from '../interface/excalidraw.interface';
import * as sqlite3 from 'sqlite3';

/**
 * Excalidraw附件服务类
 * 负责Excalidraw附件的数据库操作
 */
@Provide()
export class ExcalidrawService {
  @Inject()
  databaseService: DatabaseService;

  private db: sqlite3.Database;

  /**
   * 初始化数据库表结构
   */
  @Init()
  async init() {
    this.db = this.databaseService.getDatabase();
    await this.createTables();
  }

  /**
   * 创建Excalidraw附件表
   */
  private async createTables(): Promise<void> {
    const runAsync = promisify(this.db.run.bind(this.db));
    
    // 创建Excalidraw附件表
    const createExcalidrawAttachTableSQL = `
      CREATE TABLE IF NOT EXISTS tb_excalidraw_attachlist (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        knowledge_id INTEGER NOT NULL,
        excalidraw_json_url TEXT NOT NULL,
        image_url TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (knowledge_id) REFERENCES tb_knowledge(id) ON DELETE CASCADE
      )
    `;
    
    await runAsync(createExcalidrawAttachTableSQL);
  }

  /**
   * 根据知识库ID获取Excalidraw附件
   * @param knowledgeId 知识库ID
   * @returns Excalidraw附件或null
   */
  async getExcalidrawAttachByKnowledgeId(knowledgeId: number): Promise<IExcalidrawAttach | null> {
    const getAsync = promisify(this.db.get.bind(this.db));
    
    const result = await getAsync(
      'SELECT * FROM tb_excalidraw_attachlist WHERE knowledge_id = ?',
      [knowledgeId]
    ) as IExcalidrawAttach | undefined;
    
    return result || null;
  }

  /**
   * 创建Excalidraw附件
   * @param options 创建参数
   * @returns 创建的附件ID
   */
  async createExcalidrawAttach(options: IExcalidrawAttachCreateOptions): Promise<number> {
    const runAsync = promisify(this.db.run.bind(this.db));
    
    // 检查是否已存在该知识库的附件
    const existingAttach = await this.getExcalidrawAttachByKnowledgeId(options.knowledge_id);
    if (existingAttach) {
      // 如果已存在，则更新
      await runAsync(
        `UPDATE tb_excalidraw_attachlist 
         SET excalidraw_json_url = ?, image_url = ?, updated_at = CURRENT_TIMESTAMP 
         WHERE knowledge_id = ?`,
        [options.excalidraw_json_url, options.image_url || null, options.knowledge_id]
      );
      return existingAttach.id;
    }
    
    // 插入新附件
    await runAsync(
      'INSERT INTO tb_excalidraw_attachlist (knowledge_id, excalidraw_json_url, image_url) VALUES (?, ?, ?)',
      [options.knowledge_id, options.excalidraw_json_url, options.image_url || null]
    );
    
    // 获取最后插入的ID
    const getLastIdAsync = promisify(this.db.get.bind(this.db));
    const result = await getLastIdAsync('SELECT last_insert_rowid() as lastID') as { lastID: number };
    
    return result.lastID;
  }

  /**
   * 更新Excalidraw附件
   * @param attach 附件信息
   * @returns 是否更新成功
   */
  async updateExcalidrawAttach(attach: IExcalidrawAttach): Promise<boolean> {
    const runAsync = promisify(this.db.run.bind(this.db));
    const getAsync = promisify(this.db.get.bind(this.db));
    
    // 检查附件是否存在
    const existingAttach = await getAsync(
      'SELECT id FROM tb_excalidraw_attachlist WHERE id = ?',
      [attach.id]
    );
    
    if (!existingAttach) {
      return false;
    }
    
    // 更新附件
    let updateFields = [];
    let updateParams = [];
    
    if (attach.excalidraw_json_url !== undefined) {
      updateFields.push('excalidraw_json_url = ?');
      updateParams.push(attach.excalidraw_json_url);
    }
    
    if (attach.image_url !== undefined) {
      updateFields.push('image_url = ?');
      updateParams.push(attach.image_url);
    }
    
    if (updateFields.length > 0) {
      updateFields.push('updated_at = CURRENT_TIMESTAMP');
      
      await runAsync(
        `UPDATE tb_excalidraw_attachlist SET ${updateFields.join(', ')} WHERE id = ?`,
        [...updateParams, attach.id]
      );
    }
    
    return true;
  }

  /**
   * 删除Excalidraw附件
   * @param id 附件ID
   * @returns 是否删除成功
   */
  async deleteExcalidrawAttach(id: number): Promise<boolean> {
    const runAsync = promisify(this.db.run.bind(this.db));
    const getAsync = promisify(this.db.get.bind(this.db));
    
    // 检查附件是否存在
    const existingAttach = await getAsync(
      'SELECT id FROM tb_excalidraw_attachlist WHERE id = ?',
      [id]
    );
    
    if (!existingAttach) {
      return false;
    }
    
    // 删除附件
    await runAsync(
      'DELETE FROM tb_excalidraw_attachlist WHERE id = ?',
      [id]
    );
    
    return true;
  }
}